DROP TABLE tmpOrganizationRole
GO
SELECT A.OrganizationId, B.RoleName INTO tmpOrganizationRole
FROM OrganizationRole A
INNER JOIN Role B ON A.RoleId = B.RoleId
ORDER BY A.OrganizationId
GO
SELECT DISTINCT V1.OrganizationId, LEFT(D.ListRole, LEN(D.ListRole) - 1) AS ListRole
FROM tmpOrganizationRole V1
CROSS APPLY
(
	SELECT RoleName + ', ' AS [text()]
	FROM tmpOrganizationRole V2
	WHERE V1.OrganizationId = V2.OrganizationId	
	ORDER BY OrganizationId
	FOR XML PATH('')
) D (ListRole)
GROUP BY OrganizationId, ListRole
ORDER BY OrganizationId
